package com.biz.hunter.dao;

import com.biz.hunter.db.entity.RecommendPosition;
import com.biz.hunter.db.entity.Recommendation;
import com.biz.hunter.db.entity.RecommendedPerson;
import com.biz.hunter.db.entity.SelfRecommendPosition;
import com.biz.hunter.dbutil.BaseDAO;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

@Repository
public class RecommendDAO1 {
	public static final int PAGE_SIZE = 100;
	SimpleDateFormat df=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	private static Logger logger = LoggerFactory.getLogger(RecommendDAO1.class);
	static final String SQL_SAVE = "insert into tb_recommend(personid"
			+ ",jobid,createby,createdate,status,reason, platformid, audioid) values(?,?,?,?,?,?,?,?)";
		
	private static final String SQL_UPDATE = "update tb_recommend set status=?,updatedate=? where id =?";
		
	private static final String SQL_GET_ALL = "select * from tb_recommend";
	private static final String SQL_GET_ALL_COUNT = "select COUNT(*) from tb_recommend as r, tb_jobdesc as j where  r.jobId=j.id ";
	private static final String SQL_GET_ALL_WITH_LIMIT = "select r.*, name as jobName from tb_recommend as r, tb_jobdesc as j where r.jobId=j.id ";
	
	private static final String SQL_GETBYID = "select * from tb_recommend  where id = ?";
	private static final String SQL_DELETE = "delete from tb_recommend where id=?";
	
	private static final String SQL_GETCREATER = "select * from tb_recommend  where createby = ? order by jobid limit ?,? ";
	private static final String SQL_GET_PERSON = "select * from tb_recommend  where personid = ? order by jobid limit ?,?";
	
	private static final String SQL_GET_JOB_PERSON = "select * from tb_recommend  where jobid = ? and personid = ? and platformid=?";
	
	private static final String SQL_GET_RECOMMEND_POSITION = "SELECT distinct a.jobid AS jobid, b.name AS jobname, b.reward AS jobreward, c.name AS companyname, a.createby as createby ,b.location " +
			"from tb_recommend a, tb_jobdesc b, tb_company c where a.createby = ? and a.jobid=b.id and b.companyid=c.id and a.platformid=? limit ?,?";
	
	private static final String SQL_GET_RECOMMENDED_PERSON = "select a.id AS id, a.status AS status, b.name AS name from tb_recommend a , tb_person b "
			+ "where a.personid = b.id and a.jobid=? and a.createby =? and a.platformid=? limit ?,?";

	private static final String SQL_GET_RECOMMENDED_PERSON_2 = 
			"select a.id AS id, a.status AS status, b.name AS name, "
			+ "b.companyname as companyname,  b.companyid as companyid, "
			+ "c.name as companyname2, d.name as positionname,b.cityId "
			+ "from tb_recommend a , tb_jobtitle d, tb_person b "
			+ "left join tb_company c on b.companyid= c.id "
			+ "where a.personid = b.id and d.id=b.roleid and a.jobid=? and a.createby =? and a.platformid=? limit ?,?";
	private static final String SQL_GET_RECOMMENDED_PERSON_3 =
			"SELECT r.id AS id, r.status AS status, p.name AS name," +
					"p.companyname as companyname,  p.companyid as companyid, " +
					"c.name as companyname2, j.name as positionname,p.cityId " +
					" from tb_recommend r " +
					"LEFT JOIN tb_jobdesc j on r. jobid = j.id " +
					"LEFT JOIN tb_person p ON r.personid = p.id " +
					"LEFT JOIN tb_company c on j.companyid = c.id " +
					"WHERE r.jobid = ? " +
					"AND r.personid = ? " +
					"AND r.createby = ? " +
					"AND r.platformid = ? " +
					"LIMIT ?,?";

	private static final String SQL_GET_RECOMMEND_PERSON_S = "select a.id AS recommendid, a.status AS status, b.name AS jobname, b.reward AS jobreward, c.name AS companyname from tb_recommend a, tb_jobdesc b, tb_company c where a.personid = a.createby and a.createby = ? and a.jobid=b.id and b.companyid=c.id and a.platformid=? limit ?,?";

	private static final String SQL_UPDTE_RESOURCE = "UPDATE tb_recommend SET resid=?, resstatus=? WHERE id=?";

	private static final String SQL_UPDTE_RESOURCE_AUDIO = "UPDATE tb_recommend SET audioid=? WHERE id=?";

	private static final String SQL_GET_JOB_PHONE = "select * from tb_recommend a, tb_person b  where a.personid = b.id and a.jobid = ? and b.phone = ? ";

	private static final String SQL_GET_RECOMMEND_PERSON_BY_JOBID = "select a.id AS id, a.status AS status, b.name AS name,"
	    + " b.companyname as companyname,  b.companyid as companyid, c.name as companyname2, d.name as positionname "
	    + "from tb_recommend a , tb_jobtitle d, tb_person b "
	    + "left join tb_company c on b.companyid=c.id where a.personid = b.id and d.id=b.roleid and a.jobid=? limit ?, ?";
	private static final String SQL_GET_RECOMMEND_PERSON_ALL=
			"select a.id ,a.jobid,b.id as personid,e.empid,a.status,b.domainName,b.name,g.`name` as cityName,b.companyname,b.phone,d.`name` as roleName,b.workage,f.`name`as positionname"
		    + " from tb_recommend a , tb_jobtitle d, tb_jobdesc f,tb_person b"
		    + " left join tb_company c on b.companyid=c.id "
		    + " left join tb_employee e on b.id=e.personid "
					+"left join tb_city g on g.id = b.cityId"
		    + " where f.id=a.jobid and a.personid=b.id and d.id=b.roleid and f.createdby=? order by a.createdate DESC limit ?, ?";
	public int save(Recommendation recommend) {
		Long id = null;
		if (recommend == null) {
			logger.error("the input recommendation is null");
			return -1;
		}

		id = BaseDAO.save(SQL_SAVE, recommend.getPersonId(),
				recommend.getJobId(), recommend.getCreateby(),
				recommend.getCreateDate(), recommend.getStatus(),
				recommend.getReason(), recommend.getPlatformId(),
				recommend.getAudioid());
		if (id != null) {
			recommend.setId(id.intValue());
			return recommend.getId();
		}

		return -1;
	}

	public int delete(String recommendid) {
		if (StringUtils.isEmpty(recommendid)) {
			logger.error("please provide the recommendation id which will be removed.");
			return -1;
		}
		return BaseDAO.execute(SQL_DELETE, recommendid);
	}

	public List<Recommendation> getALL() {
		return BaseDAO.queryForList(Recommendation.class, SQL_GET_ALL);
	}
	
	public Long getCountOfAll(String name,Integer status) {
		String sql =SQL_GET_ALL_COUNT;
		if (null!= status) {
			sql += " and r.status =" + status ;
		}
		if (StringUtils.hasText(name)) {
			sql += " and name like '" + name + "'";
		}
		
		return (Long) BaseDAO.queryOneRowOneCol(sql);
	}

	public List<Recommendation> getAllWithLimit(String name, Integer status, long start, long length) {
		String sql =SQL_GET_ALL_WITH_LIMIT;
		if (StringUtils.hasText(name)) {
			sql += " and name like '" + name + "%'";
		}
		if (null != status) {
			sql += " and r.status =" + status + " ";
		}
		sql += " LIMIT ?, ?";
		return BaseDAO.queryForList(Recommendation.class, sql, start, length);
	}
	
	public int update(Recommendation rd) {
		return BaseDAO.execute(SQL_UPDATE, rd.getStatus(),df.format(new Date()), rd.getId());
	}
	
	public int updateStatus(int status, int recommendid) {
		return BaseDAO.execute(SQL_UPDATE, status,df.format(new Date()), recommendid);
	}
	
	public int updateRes(Recommendation rd) {
		return BaseDAO.execute(SQL_UPDTE_RESOURCE, rd.getResid(), rd.getResstatus(), rd.getId());
	}
	
	public int updateAudioRes(Recommendation rd) {
		return BaseDAO.execute(SQL_UPDTE_RESOURCE_AUDIO, rd.getAudioid(), rd.getId());
	}
	public Recommendation getById(String Id) {
		return BaseDAO.queryForObject(Recommendation.class, SQL_GETBYID, Id);
	}
	
	public List<Recommendation> getByCreater(int personId,int start) {
		return BaseDAO.queryForList(Recommendation.class, SQL_GETCREATER, personId,start, PAGE_SIZE);
	}
	
	public List<Recommendation> getByPerson(String personId,int start) {
		return BaseDAO.queryForList(Recommendation.class, SQL_GET_PERSON, personId, start,PAGE_SIZE);
	}

	public List<Recommendation> getByJobandPerson(long jobId, int personId, int platformid) {
		return BaseDAO.queryForList(Recommendation.class, SQL_GET_JOB_PERSON, jobId, personId, platformid);
	}
	
	public List<RecommendPosition> getRecommendPositionByCreater(int platformid, int personId, int start) {
		return BaseDAO.queryForList(RecommendPosition.class, SQL_GET_RECOMMEND_POSITION, personId, platformid, start, PAGE_SIZE);
	}
	
	public List<RecommendedPerson> getRecommendedPersonByPositionAndCreater(int platformid, int personId, int jobId, int start) {
		return BaseDAO.queryForList(RecommendedPerson.class, SQL_GET_RECOMMENDED_PERSON, jobId,  personId, platformid, start, 100);
	}
	public List<RecommendedPerson> getRecommendedPersonByPositionAndCreater(int platformid, int personId, int jobId, int start,int length) {
		return BaseDAO.queryForList(RecommendedPerson.class, SQL_GET_RECOMMENDED_PERSON, jobId, personId, platformid, start, length);
	}

	public List<RecommendedPerson> getRecommendedPersonByPositionAndCreater2(int platformid, int personId, long jobId, int start) {
		return BaseDAO.queryForList(RecommendedPerson.class, SQL_GET_RECOMMENDED_PERSON_2, jobId, personId, platformid, start, PAGE_SIZE);
	}

	public List<RecommendedPerson> getRecommendedPersons(int platformid, int createdBy, long jobId,int personId, int start) {
		return BaseDAO.queryForList(RecommendedPerson.class, SQL_GET_RECOMMENDED_PERSON_3, jobId, personId, createdBy, platformid, start, PAGE_SIZE);
	}

	public List<SelfRecommendPosition> getSelfRecommendPositionByCreater(int platformid, int personId, int start) {
		return BaseDAO.queryForList(SelfRecommendPosition.class, SQL_GET_RECOMMEND_PERSON_S, personId, platformid, start, PAGE_SIZE);
	}
	private static final String SQL_GET_JOB_PHONE_PLAT = "select * from tb_recommend a, tb_person b  where a.personid = b.id "
			+ "and a.jobid = ? and b.phone = ? and platformid =? ";

	public List<Recommendation> getByJobAndPhone(long jobId, String phone, int platformid) {
		return BaseDAO.queryForList(Recommendation.class, SQL_GET_JOB_PHONE_PLAT, jobId, phone, platformid);
	}
	 public List<Recommendation> getByJobAndPhone(long jobId, String phone) {
	    return BaseDAO.queryForList(Recommendation.class, SQL_GET_JOB_PHONE, jobId, phone);
	  }
	
	public List<RecommendedPerson> getRecommendedPersonByPosition(long jobId, int start, int length) {
		return BaseDAO.queryForList(RecommendedPerson.class, SQL_GET_RECOMMEND_PERSON_BY_JOBID, jobId, start, length);
	}

	public List<RecommendedPerson> getRecommendedPersonByPosition(long jobId, int start) {
		return BaseDAO.queryForList(RecommendedPerson.class, SQL_GET_RECOMMEND_PERSON_BY_JOBID, jobId, start, PAGE_SIZE);
	}
	
	public List<RecommendedPerson> getRecommendedPersonAll(long createid,int start){
		return BaseDAO.queryForList(RecommendedPerson.class, SQL_GET_RECOMMEND_PERSON_ALL,createid,start,PAGE_SIZE);
	}

	public static final String SQL_GET_DIY = "select * from tb_recommend where personid = ? and jobid = ? and status = ? limit 0,1";
	public Recommendation getByStatusAndJobIdAndPersonId(int personId, int jobid, int status) {
		return BaseDAO.queryForObject(Recommendation.class,SQL_GET_DIY,personId,jobid,status);
	}

}
